﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SMALLERP.DataClass;
using System.Data.SqlClient;
using SMALLERP;
using XJLSC.PM;
using System.IO;
using Microsoft.VisualBasic;
using SMALLERP.ComClass;

namespace XJLSC.FM
{
    public partial class TRCCHX : Form
    {
        private readonly DataBase db = new DataBase();
        private SqlDataReader sdr1;

        public TRCCHX()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strSql = "";
            if (checkBox1.CheckState == CheckState.Checked)
            {
                if (checkBox2.CheckState == CheckState.Unchecked)
                {
                    if (checkBox3.CheckState == CheckState.Unchecked)
                    {
                        if (checkBox4.CheckState == CheckState.Unchecked)
                        {
                            strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.GDDB LIKE '51%' AND B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE A.GDDB LIKE '51%' AND (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                        }
                        else
                        {
                            strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                        }
                     }
                    else
                    {
                        if (checkBox4.CheckState == CheckState.Unchecked)
                        {
                            strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' GROUP BY SCDW,DDDH,PH,PM,GG";
                        }
                        else
                        {
                            strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' GROUP BY SCDW,DDDH,PH,PM,GG";
                        }
                     }
                 }
                else
                {
                    if (textBox1.Text == "1024")
                    {
                        if (checkBox3.CheckState == CheckState.Unchecked)
                        {
                            if (checkBox4.CheckState == CheckState.Unchecked)
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                            else
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                         }
                        else
                        {
                            if (checkBox4.CheckState == CheckState.Unchecked)
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                            else
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                         }
                       }
                    else
                    {
                        if (checkBox3.CheckState == CheckState.Unchecked)
                        {
                            if (checkBox4.CheckState == CheckState.Unchecked)
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCTB LEFT JOIN MOCTA ON TB001=TA001 AND TB002=TA002 WHERE RTRIM(TB003)=RTRIM(PH) AND TA011<>'Y' AND TA011<>'y' AND TA027=DDDH) AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                            else
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCTB LEFT JOIN MOCTA ON TB001=TA001 AND TB002=TA002 WHERE RTRIM(TB003)=RTRIM(PH) AND TA011<>'Y' AND TA011<>'y' AND TA027=DDDH) AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH)) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                          }
                        else
                        {
                            if (checkBox4.CheckState == CheckState.Unchecked)
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCTB LEFT JOIN MOCTA ON TB001=TA001 AND TB002=TA002 WHERE RTRIM(TB003)=RTRIM(PH) AND TA011<>'Y' AND TA011<>'y' AND TA027=DDDH) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                            else
                            {
                                strSql = "SELECT SCDW 生产单位,DDDH 订单单号,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND ISNULL(DDDH,'')<>'' AND NOT EXISTS(SELECT * FROM MOCTB LEFT JOIN MOCTA ON TB001=TA001 AND TB002=TA002 WHERE RTRIM(TB003)=RTRIM(PH) AND TA011<>'Y' AND TA011<>'y' AND TA027=DDDH) GROUP BY SCDW,DDDH,PH,PM,GG";
                            }
                            }
                        }
                }
             }
            else
            {
                if (checkBox3.CheckState == CheckState.Unchecked)
                {
                    if (checkBox4.CheckState == CheckState.Unchecked)
                    {
                        strSql = "SELECT SCDW 生产单位,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH))  GROUP BY SCDW,PH,PM,GG";
                    }
                    else
                    {
                        strSql = "SELECT SCDW 生产单位,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%' AND NOT EXISTS(SELECT * FROM MOCVZ WHERE VZ001=SCDW AND VZ002=DDDH AND VZ004=RTRIM(PH))  GROUP BY SCDW,PH,PM,GG";
                    }
                    }
                else
                {
                    if (checkBox4.CheckState == CheckState.Unchecked)
                    {
                        strSql = "SELECT SCDW 生产单位,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))-CEILING(SUM(ZCRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%'  GROUP BY SCDW,PH,PM,GG";
                    }
                    else
                    {
                        strSql = "SELECT SCDW 生产单位,RTRIM(PH) 品号,RTRIM(PM) 品名,RTRIM(GG) 规格,ISNULL((SELECT CEILING(SUM(QCSY))+CEILING(SUM(ZCLL))+CEILING(SUM(FGLL))-CEILING(SUM(ZCRK))-CEILING(SUM(FGRK))-CEILING(SUM(GFSL))-CEILING(SUM(LFSL))-CEILING(SUM(BLSL)) FROM V_BTHX B WHERE B.SCDW=A.SCDW AND B.PH=A.PH AND B.JYRQ<CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112)),0) 期初在制 ,CEILING(SUM(ZCLL)) 正常领料,CEILING(SUM(FGLL)) 返工领料,CEILING(SUM(ZCRK)) 正常入库,CEILING(SUM(FGRK)) 返工入库,CEILING(SUM(GFSL)) 工废数量,CEILING(SUM(LFSL)) 料废数量,CEILING(SUM(BLSL)) 不良数量,0 AS 期末结存 FROM V_BTHX A WHERE (SCDW='" + textBox1.Text + "' OR '" + textBox1.Text + "'='') AND JYRQ>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox2.Text + "'),112) AND (JYRQ<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + textBox3.Text + "'),112) OR '" + textBox3.Text + "'='') AND PM LIKE '%" + textBox4.Text + "%' AND DDDH LIKE '%" + textBox5.Text + "%'  GROUP BY SCDW,PH,PM,GG";
                    }
                 }
                }  
               
                //MessageBox.Show(strSql,"提示");
                try
                {
                    DataTable dt;
                    dataGridView1.DataSource = "";
                    dataGridView1.Refresh();

                    dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                    dt.Rows.Add();
                    dataGridView1.DataSource = dt;

                    //this.dataGridView1.Columns["工单别"].Visible = false;
                    //this.dataGridView1.Columns["工单号"].Visible = true;
                    //this.dataGridView1.Columns["工单号"].Width = 80;
                    //this.dataGridView1.Columns["产品品号"].Visible = true;
                    //this.dataGridView1.Columns["产品品号"].Width = 80;
                    //this.dataGridView1.Columns["订单号"].Width = 50;
                    //this.dataGridView1.Columns["工艺"].Visible = false;
                    //this.dataGridView1.Columns["已入库量"].Visible = true;
                    //this.dataGridView1.Columns["已入库量"].Width = 30;
                    //this.dataGridView1.Columns["产品品号"].Width = 50;
                    //this.dataGridView1.Columns["设备名称"].Visible = false;
                    //this.dataGridView1.Columns["班产量"].Visible = false;
                    //this.dataGridView1.Columns["品名"].ReadOnly = true;
                    //this.dataGridView1.Columns["品名"].Frozen = true;
                    //this.dataGridView1.Columns["品名"].Visible = true;
                    //this.dataGridView1.Columns["品名"].Width = 50;
                    //this.dataGridView1.Columns["规格"].ReadOnly = true;
                    //this.dataGridView1.Columns["规格"].Width = 100;
                    //this.dataGridView1.Columns["规格"].Frozen = true;
                    //this.dataGridView1.Columns["工序号"].ReadOnly = true;
                    //this.dataGridView1.Columns["工序号"].Visible = false;
                    //this.dataGridView1.Columns["工序号"].Frozen = true;
                    //this.dataGridView1.Columns["工艺名称"].ReadOnly = true;
                    //this.dataGridView1.Columns["工艺名称"].Visible = true;
                    //this.dataGridView1.Columns["工艺名称"].Frozen = true;
                    //this.dataGridView1.Columns["工艺名称"].Width = 50;
                    //this.dataGridView1.Columns["订单备注"].Visible = false;
                    //this.dataGridView1.Columns["数量"].ReadOnly = true;
                    //this.dataGridView1.Columns["数量"].Frozen = true;
                    //this.dataGridView1.Columns["数量"].Width = 50;
                    //this.dataGridView1.Columns["已排产量"].ReadOnly = true;
                    //this.dataGridView1.Columns["已排产量"].Frozen = true;
                    //this.dataGridView1.Columns["已排产量"].Width = 50;
                    //this.dataGridView1.Columns["总需求量"].Width = 50;
                    //this.dataGridView1.Columns["工作中心"].Frozen = true;
                    //this.dataGridView1.Columns["工作中心"].Visible = false;
                    //this.dataGridView1.Columns["设备名称"].ReadOnly = true;
                    //this.dataGridView1.Columns["设备名称"].Frozen = true;
                    //this.dataGridView1.Columns["设备"].Frozen = true;
                    //this.dataGridView1.Columns["设备"].Visible = false;
                    //this.dataGridView1.Columns["库存量"].Visible = false;
                    //this.dataGridView1.Columns["齐套率"].DefaultCellStyle.Format = "P2";
                    //this.dataGridView1.Columns["齐套率"].Width = 50;
                    foreach (DataGridViewRow dr in this.dataGridView1.Rows)
                    {
                         double sum2 = 0;
                    if (checkBox4.CheckState == CheckState.Unchecked)
                    {
                        sum2 = double.Parse(dr.Cells["期初在制"].Value == null ? "0" : dr.Cells["期初在制"].Value.ToString()) + double.Parse(dr.Cells["正常领料"].Value == null ? "0" : dr.Cells["正常领料"].Value.ToString()) - double.Parse(dr.Cells["正常入库"].Value == null ? "0" : dr.Cells["正常入库"].Value.ToString()) - double.Parse(dr.Cells["工废数量"].Value == null ? "0" : dr.Cells["工废数量"].Value.ToString()) - double.Parse(dr.Cells["料废数量"].Value == null ? "0" : dr.Cells["料废数量"].Value.ToString()) - double.Parse(dr.Cells["不良数量"].Value == null ? "0" : dr.Cells["不良数量"].Value.ToString());
                    }
                    else
                    {
                        sum2 = double.Parse(dr.Cells["期初在制"].Value == null ? "0" : dr.Cells["期初在制"].Value.ToString()) + double.Parse(dr.Cells["正常领料"].Value == null ? "0" : dr.Cells["正常领料"].Value.ToString()) + double.Parse(dr.Cells["返工领料"].Value == null ? "0" : dr.Cells["返工领料"].Value.ToString()) - double.Parse(dr.Cells["正常入库"].Value == null ? "0" : dr.Cells["正常入库"].Value.ToString()) - double.Parse(dr.Cells["返工入库"].Value == null ? "0" : dr.Cells["返工入库"].Value.ToString()) - double.Parse(dr.Cells["工废数量"].Value == null ? "0" : dr.Cells["工废数量"].Value.ToString()) - double.Parse(dr.Cells["料废数量"].Value == null ? "0" : dr.Cells["料废数量"].Value.ToString()) - double.Parse(dr.Cells["不良数量"].Value == null ? "0" : dr.Cells["不良数量"].Value.ToString());
                    }
                        dr.Cells["期末结存"].Value = Convert.ToString(sum2.ToString());

                    }
                    //this.dataGridView1.DefaultCellStyle.BackColor = Color.Yellow;
                    this.dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
                    this.dataGridView1.AllowUserToAddRows = true;

                }
                catch 
                {
                    
                }
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            textBox2.Text = dateTimePicker1.Value.ToShortDateString();
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            textBox3.Text = dateTimePicker2.Value.ToShortDateString();
        }

        private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            string x1 = "";
            string x2 = "";
            string x3 = "";
            string x4 = "";
            string x5 = "";
            string x6 = "";
            x1 = dataGridView1["品号", dataGridView1.CurrentCell.RowIndex].Value.ToString();
            x2 = textBox2.Text;
            x3 = textBox3.Text;
            x4 = textBox1.Text;
            if (checkBox1.CheckState == CheckState.Checked)
            {
                x5 = dataGridView1["订单单号", dataGridView1.CurrentCell.RowIndex].Value.ToString();
            }
            else
            {
                x5 = textBox5.Text;
            }
            if (checkBox4.CheckState == CheckState.Checked)
            {
                if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["正常领料"].Index)
                {
                    x6 = "正常领料";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["返工领料"].Index)
                {
                    x6 = "返工领料";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["正常入库"].Index)
                {
                    x6 = "正常入库";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["返工入库"].Index)
                {
                    x6 = "返工入库";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["工废数量"].Index)
                {
                    x6 = "工废数量";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["料废数量"].Index)
                {
                    x6 = "料废数量";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["不良数量"].Index)
                {
                    x6 = "不良数量";

                }

                else
                {
                    x6 = "其他";
                    MessageBox.Show("本列无明细数据", "软件提示");
                }

                if (x6 != "其他")
                {
                    HXMXCX xx = new HXMXCX(x1, x2, x3, x4, x5, x6);
                    CHAXUN f = new CHAXUN();
                    f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                    f.Show(this);
                }
            }
            else
            {
                if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["正常领料"].Index)
                {
                    x6 = "正常领料";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["正常入库"].Index)
                {
                    x6 = "正常入库";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["工废数量"].Index)
                {
                    x6 = "工废数量";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["料废数量"].Index)
                {
                    x6 = "料废数量";

                }
                else if (e.RowIndex > -1 && e.ColumnIndex == this.dataGridView1.Columns["不良数量"].Index)
                {
                    x6 = "不良数量";

                }

                else
                {
                    x6 = "其他";
                    MessageBox.Show("本列无明细数据", "软件提示");
                }

                if (x6 != "其他")
                {
                    HXMXCX xx = new HXMXCX(x1, x2, x3, x4, x5, x6);
                    CHAXUN f = new CHAXUN();
                    f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                    f.Show(this);
                }

            }
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void TRCCHX_Load(object sender, EventArgs e)
        {
            if (checkBox1.CheckState == CheckState.Checked)
            {
                checkBox2.Enabled = true;
            }
            else
            {
                checkBox2.Enabled = false;
            }

            if (checkBox2.CheckState == CheckState.Checked)
            {
                button3.Enabled = true;
                button4.Enabled = true;
            }
            else
            {
                button3.Enabled = false;
                button4.Enabled = false;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (dataGridView1.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView1.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView1.Columns.Count; k++)
                        {
                            if (dataGridView1.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView1.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.CheckState == CheckState.Checked)
            {
                checkBox2.Enabled = true;
            }
            else
            {
                checkBox2.Enabled = false;
            }
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox2.CheckState == CheckState.Checked)
            {
               button3.Enabled = true;
               button4.Enabled = true;
            }
            else
            {
               button3.Enabled = false;
               button4.Enabled = false;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //if(PropertyClass.jiaose !="稽核员")
            //{
            //    MessageBox.Show("对不起,你没有处理数据的权限！", "系统提示");
            //    return;
            //}
            //string clyj = Interaction.InputBox("请输入处理原因：", "系统提示", "", -1, -1);
            //if(clyj=="")
            //{
            //    MessageBox.Show("处理意见不能为空！", "系统提示");
            //    return;
            //}
            //string scdw = (dataGridView1.CurrentRow.Cells["生产单位"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["生产单位"].Value.ToString();
            //string dddh = (dataGridView1.CurrentRow.Cells["订单单号"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["订单单号"].Value.ToString();
            //string ddph = (dataGridView1.CurrentRow.Cells["品号"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["品号"].Value.ToString();

            //string strSql = "";
            //strSql = "INSERT INTO MOCVZ  SELECT '" + PropertyClass.OperatorCode + "',CONVERT(CHAR(20),GETDATE(),120),'" + scdw + "','" + dddh + "','" + clyj + "','" + ddph + "'";
            //try
            //{
            //    if (db.ExecDataBySql1(strSql) > 0)
            //    {
            //        MessageBox.Show("处理成功!", "系统提示");
            //    }
            //    else
            //    {
            //        MessageBox.Show("处理失败!", "系统提示");
            //        return;
            //    }
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message, "软件提示");
            //    throw ex;
            //}

        }

        private void button4_Click(object sender, EventArgs e)
        {
            if(MessageBox.Show("确定要撤销本笔数据处理吗?","系统提示",MessageBoxButtons.YesNo)==DialogResult.Yes)
            {
                string scdw = (dataGridView1.CurrentRow.Cells["生产单位"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["生产单位"].Value.ToString();
                string dddh = (dataGridView1.CurrentRow.Cells["订单单号"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["订单单号"].Value.ToString();
                string ddph = (dataGridView1.CurrentRow.Cells["品号"].Value == null) ? "" : dataGridView1.CurrentRow.Cells["品号"].Value.ToString();

                string strSql = "";
                strSql = "DELETE FROM MOCVZ WHERE VZ001='" + scdw + "' AND VZ002='" + dddh + "' AND VZ004='" + ddph + "'";
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                        MessageBox.Show("撤销成功!", "系统提示");
                    }
                    else
                    {
                        MessageBox.Show("撤销失败!", "系统提示");
                        return;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "软件提示");
                    throw ex;
                }
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            String strSql = "";
            String xxny = "";
            strSql = "SELECT MAX(LA002) AS LA002 FROM DRPLA";
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    xxny = sdr1["LA002"].ToString();
                }
                if (sdr1.HasRows == false)
                {
                    xxny = "";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT 生产单位,单位名称,RTRIM(材料品号) 材料品号,品名,规格,CEILING(SUM(期初数量)) 期初数量,CEILING(SUM(本期投入)) 本期投入,CEILING(SUM(本期产出)) 本期产出,CEILING(SUM(料废数量)) 料废数量,CEILING(SUM(工废数量)) 工废数量,CEILING(SUM(不良数量)) 不良数量,0 AS 结存数 FROM (SELECT TA003 下单日期,TA001 单别,TA002 单号,(CASE WHEN TA030='1' THEN RTRIM(TA021) ELSE RTRIM(TA032) END) 生产单位,(CASE WHEN TA030='1' THEN RTRIM(MD002) ELSE RTRIM(MA002) END) 单位名称,(CASE WHEN TB003 LIKE '1%' THEN TA006 ELSE TB003 END) 材料品号,MB002 品名,MB003 规格,(SELECT ";
                   strSql += " ISNULL(SUM(TRSL-CCSL),0) FROM V_TRCCMX WHERE TE011=TA001 AND TE012=TA002 AND TE004=TB003 AND TC003>'" + xxny + "' AND TC003<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)) 期初数量,(SELECT CAST(ISNULL(SUM(TE005),0) AS REAL) FROM MOCTE LEFT JOIN MOCTC ON TE001=TC001 AND TE002=TC002 WHERE TC003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TC003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TE011=TA001 AND TE012=TA002 AND TE004=TB003) 本期投入,(SELECT CAST(ISNULL(SUM(TG013),0) AS REAL) FROM MOCTG LEFT JOIN MOCTF ON TF001=TG001 AND TF002=TG002 LEFT JOIN MOCTB B ON TG014=B.TB001 AND TG015=B.TB002 WHERE TF003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TF003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TG014=TA001 AND TG015=TA002 AND B.TB003=MOCTB.TB003)+(SELECT CAST(ISNULL(SUM(TI019),0) AS REAL) FROM MOCTI LEFT JOIN MOCTH ON TI001=TH001 AND TI002=TH002 LEFT JOIN MOCTB B ON TI013=B.TB001 AND TI014=B.TB002 WHERE TH003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TH003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TI013=TA001 AND TI014=TA002 AND B.TB003=MOCTB.TB003) 本期产出,CAST((SELECT ISNULL(SUM(UDF51),0) FROM SFCTE WHERE TE006=TA001 AND TE007=TA002 AND TE001='D410' AND TE023>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TE023<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)) AS REAL) 料废数量,CAST((SELECT ISNULL(SUM(UDF52),0) FROM SFCTE WHERE TE006=TA001 AND TE007=TA002 AND TE002='D410' AND TE023>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TE023<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)) AS REAL) 工废数量,CAST((SELECT ISNULL(SUM(UDF53),0) FROM SFCTE WHERE TE006=TA001 AND TE007=TA002 AND TE002='D410' AND TE023>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TE023<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)) AS REAL) 不良数量,0 结存数 FROM MOCTA LEFT JOIN MOCTB ON TA001=TB001 AND TA002=TB002 LEFT JOIN CMSMD ON TA021=MD001 LEFT JOIN PURMA ON TA032=MA001 LEFT JOIN INVMB ON MB001=(CASE WHEN TB003 LIKE '1%' THEN TA006 ELSE TB003 END) ";
                   strSql += " WHERE (TB003 LIKE '2%' OR TB003 LIKE '1%' OR TB003 LIKE '4%') AND (CASE WHEN TA030='1' THEN RTRIM(TA021) ELSE RTRIM(TA032) END) LIKE '%" + textBox6.Text + "%' AND TB005> 0 AND (EXISTS(SELECT * FROM MOCTE LEFT JOIN MOCTC ON TE001=TC001 AND TE002=TC002 WHERE TC003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TC003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TA001=TE011 AND TA002=TE012) OR EXISTS(SELECT * FROM MOCTG LEFT JOIN MOCTF ON TG001=TF001 AND TG002=TF002 WHERE TG003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TG003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TA001=TG014 AND TA002=TG015) OR EXISTS(SELECT * FROM MOCTI LEFT JOIN MOCTH ON TH001=TI001 AND TH002=TI002 WHERE TH003>=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND TH003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND TA001=TI013 AND TA002=TI014))"; 
                   //strSql += " UNION ALL SELECT TL003,'','',(CASE WHEN TL006='5080' THEN '1008' WHEN TL006='5010' THEN '1001' WHEN TL006='5020' THEN '1002' ELSE '' END),RTRIM(MD002),TM004,MB002,MB003,TM007,0,0,0,0,0,0 FROM INVTM LEFT JOIN INVTL ON TM001=TL001 AND TM002=TL002 LEFT JOIN INVMB ON TM004=MB001 LEFT JOIN CMSMD ON MD001=(CASE WHEN TL006='5080' THEN '1008' WHEN TL006='5010' THEN '1001' WHEN TL006='5020' THEN '1002' ELSE '' END) WHERE TL003<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)  AND (CASE WHEN TL006='5080' THEN '1008' WHEN TL006='5010' THEN '1001' WHEN TL006='5020' THEN '1002' ELSE '' END)<>'' AND (CASE WHEN TL006='5080' THEN '1008' WHEN TL006='5010' THEN '1001' WHEN TL006='5020' THEN '1002' ELSE '' END) LIKE '%" + textBox6.Text + "%' AND TM004 NOT LIKE '8%'";
                   //strSql += " UNION ALL SELECT TL003,'','',(CASE WHEN TL006='5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END),RTRIM(CMSMD.MD002),BOMMD.MD003,MB002,MB003,TM007*BOMMD.MD006/BOMMD.MD007,0,0,0,0,0,0 FROM INVTM LEFT JOIN INVTL ON TM001 = TL001 AND TM002 = TL002 LEFT JOIN BOMMD ON TM004 = BOMMD.MD001 LEFT JOIN INVMB ON BOMMD.MD003 = MB001 LEFT JOIN CMSMD ON CMSMD.MD001 = (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) WHERE TL003<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND(CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) <> '' AND (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) LIKE '%" + textBox6.Text + "%' AND TM004 LIKE '8%' AND BOMMD.MD003 NOT LIKE '4%'";
                   strSql += " UNION ALL SELECT TL003,'','',(CASE WHEN TL006='5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END),RTRIM(MD002),TM004,MB002,MB003,0,0,0,0,TM007,0,0 FROM INVTM LEFT JOIN INVTL ON TM001 = TL001 AND TM002 = TL002 LEFT JOIN INVMB ON TM004 = MB001 LEFT JOIN CMSMD ON MD001 = (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) WHERE TL003>= CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)  AND TL003<= CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)  AND(CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) <> '' AND(CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) LIKE '%" + textBox6.Text + "%' AND TM004 NOT LIKE '8%'";
                   strSql += " UNION ALL SELECT TL003,'','',(CASE WHEN TL006='5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END),RTRIM(CMSMD.MD002),BOMMD.MD003,MB002,MB003,0,0,0,0,TM007*BOMMD.MD006/BOMMD.MD007,0,0 FROM INVTM LEFT JOIN INVTL ON TM001 = TL001 AND TM002 = TL002 LEFT JOIN BOMMD ON TM004 = BOMMD.MD001 LEFT JOIN INVMB ON BOMMD.MD003 = MB001 LEFT JOIN CMSMD ON CMSMD.MD001 = (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) WHERE TL003>= CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112)  AND TL003<=CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112)  AND (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) <> '' AND (CASE WHEN TL006 = '5080' THEN '1008' WHEN TL006 = '5010' THEN '1001' WHEN TL006 = '5020' THEN '1002' ELSE '' END) LIKE '%" + textBox6.Text + "%' AND TM004 LIKE '8%' AND BOMMD.MD003 NOT LIKE '4%'";
                   strSql += " UNION ALL SELECT LA002,'','',LA001,MD002,LA003,MB002,MB003,LA004,0,0,0,0,0,0 FROM DRPLA A LEFT JOIN CMSMD ON MD001=LA001 LEFT JOIN INVMB ON MB001=LA003 WHERE LA002<CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND NOT EXISTS(SELECT * FROM MRPLA B WHERE A.LA001=B.LA001 AND A.LA003=B.LA003 AND A.LA002<B.LA002)";
                   strSql += " ) A GROUP BY 生产单位,单位名称,材料品号,品名,规格";
            //textBox5.Text = strSql;
            try
            {
                DataTable dt;
                dt = db.GetDataSet1(strSql, "MOCTB").Tables["MOCTB"];
                dataGridView2.DataSource = dt;
            }
            catch
            {

            }

            try
            {
                double jcsl;
                progressBar1.Visible = true;
                progressBar1.Maximum = dataGridView2.Rows.Count - 1;//设置最大长度值
                progressBar1.Value = 0;//设置当前值
                progressBar1.Step = 1;//设置没次增长多少
                for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)
                {
                    progressBar1.Value += progressBar1.Step;
                    double qcsl = Convert.ToDouble(dataGridView2.Rows[i].Cells["期初数量"].Value.ToString());
                    double bqtr = Convert.ToDouble(dataGridView2.Rows[i].Cells["本期投入"].Value.ToString());
                    double bqcc = Convert.ToDouble(dataGridView2.Rows[i].Cells["本期产出"].Value.ToString());
                    double lfsl = Convert.ToDouble(dataGridView2.Rows[i].Cells["料废数量"].Value.ToString());
                    double gfsl = Convert.ToDouble(dataGridView2.Rows[i].Cells["工废数量"].Value.ToString());
                    double blsl = Convert.ToDouble(dataGridView2.Rows[i].Cells["不良数量"].Value.ToString());
                    jcsl = qcsl + bqtr - bqcc - lfsl - gfsl - blsl;
                    dataGridView2.Rows[i].Cells["结存数"].Value = jcsl.ToString();
                }
                progressBar1.Visible = false;
            }
            catch
            {

            }

        }

        private void button5_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.Filter = "Execl files (*.xls)|*.xls";
            dlg.FilterIndex = 0;
            dlg.RestoreDirectory = true;
            dlg.CreatePrompt = true;
            dlg.Title = "保存为Excel文件";
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = dlg.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                string columnTitle = null;
                try
                {
                    //写入列标题    
                    for (int i = 0; i < dataGridView2.ColumnCount; i++)
                    {
                        if (dataGridView2.Columns[i].Visible == true)
                        {
                            if (i > 0)
                            {
                                columnTitle += "\t";
                            }
                            columnTitle += dataGridView2.Columns[i].HeaderText;

                        }

                    }
                    sw.WriteLine(columnTitle);
                    //写入列内容    
                    for (int j = 0; j < dataGridView2.Rows.Count; j++)
                    {

                        string columnValue = null;
                        for (int k = 0; k < dataGridView2.Columns.Count; k++)
                        {
                            if (dataGridView2.Columns[k].Visible == true)
                            {
                                if (k > 0)
                                {
                                    columnValue += "\t";
                                }
                                if (dataGridView2.Rows[j].Cells[k].Value == null)
                                    columnValue += "";
                                else
                                    columnValue += dataGridView2.Rows[j].Cells[k].Value.ToString().Trim();
                            }
                        }
                        sw.WriteLine(columnValue);
                    }
                    sw.Close();
                    myStream.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }

        private void dataGridView2_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }
    }
}
